package QRCG;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class DatabaseManager {
	private Connection connection = null;
	private ResultSet resultset = null;
	private Statement statement = null;

	public enum UserInformation {
		EMAIL("email"), ID("user_id"), NAME("name"), SURNAME("surname"), LOCATION_ID("location_id"), LOCATION(
				"location"), GENDER("is_male");

		private final String columnName;

		UserInformation(String columnName) {
			this.columnName = columnName;
		}

		public String queryString() {
			return this.columnName;
		}
	}

	public enum GameInformation {
		NAME("name"), RATING("rating"), BEGIN_DATE("begin_date"), END_DATE("end_date"), DESCRIPTION("description"), LEVEL_COUNT(
				"level_count"), LOCATION_ID("location_id"), LOCATION("location"), CREATOR_ID("creator_id"), GAME_TYPE(
				"game_type"), IS_ACTIVE("is_active");

		private final String columnName;

		GameInformation(String columnName) {
			this.columnName = columnName;
		}

		public String queryString() {
			return this.columnName;
		}
	}

	public enum LevelInformation {
		GAME_ID("game_id"), LEVEL_ID("level_id"), LEVEL_CODE("level_code"), HINT("hint"), QR_ADDRESS("qr_address");

		private final String columnName;

		LevelInformation(String columnName) {
			this.columnName = columnName;
		}

		public String queryString() {
			return this.columnName;
		}
	}

	/**
	 * Connects to the database.
	 * 
	 * @throws SQLException
	 */
	public void connect() throws SQLException {
		Driver driver = new com.mysql.jdbc.Driver();
		DriverManager.registerDriver(driver);
		Properties p = new Properties();

		// System.out.println("Driver Loaded Successfully ...");

		// for localhost:
		// final String connectionUrl = "jdbc:mysql://localhost/database2";
		// p.put("user", "root");
		// p.put("password", "root");

		final String connectionUrl = "jdbc:mysql://titan.cmpe.boun.edu.tr/database2";
		p.put("user", "project2");
		p.put("password", "k2uy8");

		connection = DriverManager.getConnection(connectionUrl, p);

		if (connection == null) {
			System.out.println("Database connection failed");
		} else {
			connection.setAutoCommit(true);
			System.out.println("Successfully Connected to Database...");
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
		}
	}

	public Connection getConnection() {
		return connection;
	}

	public String returnSingleResultRow(String query) throws SQLException {
		try {
			statement = connection.createStatement();
			resultset = statement.executeQuery(query);
			if (resultset.next()) {
				return resultset.getString(1);
			}
		} catch (SQLException se) {
			System.out.println("Query failed: " + query);
			se.printStackTrace();
		} finally {
			statement.close();
			resultset.close();
		}
		return null;
	}

	/**
	 * 
	 * @param gameID
	 * @return The user_id of the player that has finished the game with game id
	 *         of {@code gameID} in the shortest period of time is returned.
	 */
	public String gameWinner(int gameID) {

		String query = "SELECT gp.user_id FROM GamePlayers gp WHERE gp.game_id="
				+ gameID
				+ " AND gp.user_begins IS NOT NULL AND gp.user_ends IS NOT NULL ORDER BY TIMEDIFF(gp.user_ends, gp.user_begins) ASC LIMIT 1";

		System.out.println("Game winner query: " + query);
		try {
			return returnSingleResultRow(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Error in gameWinner method");
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 
	 * @param informationType
	 * @param nickname
	 * @return The information piece as specified by the {@code informationType}
	 *         . If it is {@code UserInformation.GENDER}, then "male", "female"
	 *         or {@code null} is returned.
	 */
	public String getUserInformation(UserInformation informationType, String nickname) {
		String query;
		if (informationType == UserInformation.LOCATION) {
			query = "SELECT L.location_name FROM Users U,Locations L WHERE (U.location_id = L.location_id AND U.nickname = '"
					+ nickname + "')";
		} else {
			query = "SELECT " + informationType.queryString() + " FROM Users WHERE nickname = '" + nickname + "'";
		}

		try {
			String result = returnSingleResultRow(query);
			if (informationType == UserInformation.GENDER && Util.hasTextContent(result)) {
				result = result.compareTo("1") == 0 ? "male" : "female";
			}
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 
	 * @param informationType
	 * @param nickname
	 * @return The information piece as specified by the {@code informationType}
	 *         . If it is {@code UserInformation.GENDER}, then "male", "female"
	 *         or {@code null} is returned.
	 */
	public String getGameInformation(GameInformation informationType, String gameId) {
		String query;
		if (informationType == GameInformation.LOCATION) {
			query = "SELECT L.location_name FROM Games G,Locations L WHERE (G.location_id = L.location_id AND G.game_id = '"
					+ gameId + "')";
		} else {
			query = "SELECT " + informationType.queryString() + " FROM Games WHERE game_id = '" + gameId + "'";
		}

		try {
			String result = returnSingleResultRow(query);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * @return The list of all locations in the database.
	 */
	public ArrayList<HashMap<String, Object>> getAllLocations() {
		String query = "SELECT location_name,location_id FROM Locations ";
		return executeSelectQuery(query);
	}

	/**
	 * @return The list of top {@code limit} active games is returned.
	 */
	public ArrayList<HashMap<String, Object>> getSortedGames(String sort, String today, int limit) {
		String query = "SELECT * FROM Games WHERE is_active =1 AND end_date >= '" + today + "' ORDER BY " + sort
				+ " DESC LIMIT " + limit;
		System.out.println("Limited sorted active games query: " + query);
		return executeSelectQuery(query);

	}

	/**
	 * @return The list of active games.
	 */
	public ArrayList<HashMap<String, Object>> getOngoingGames() {
		String query = "SELECT * FROM Games WHERE is_active =1";
		System.out.println("All active games query: " + query);
		return executeSelectQuery(query);

	}

	/**
	 * @return The list of all games alphabetically sorted.
	 */
	public ArrayList<HashMap<String, Object>> getAllGames() {
		String query = "SELECT Games.game_id, Games.name, Games.creator_id, Games.begin_date, Games.end_date, Games.description, Locations.location_name FROM Games, Locations WHERE Games.location_id = Locations.location_id ORDER BY Games.name";
		System.out.println("All games query: " + query);
		return executeSelectQuery(query);

	}

	/**
	 * @param id
	 * @param limit
	 * @return The list of the top {@code limit} games ordered by rating that
	 *         the user with {@code id} plays(ed).
	 */
	public ArrayList<HashMap<String, Object>> getUserGames(String id, int limit) {
		String query = "";
		if (limit != 0)
			query = "SELECT g.* FROM Games g, GamePlayers p WHERE p.game_id= g.game_id AND p.user_id = " + id
					+ " ORDER BY g.rating DESC LIMIT " + limit;
		else
			query = "SELECT g.* FROM Games g, GamePlayers p WHERE p.game_id= g.game_id AND p.user_id = " + id
					+ " ORDER BY g.rating DESC ";

		System.out.println("Get user games query: " + query);

		return executeSelectQuery(query);
	}

	/**
	 * @param searchval
	 * @param scriteria
	 * @return The list of games that have {@code searchval} in their
	 *         {@code sciteria}
	 */
	public ArrayList<HashMap<String, Object>> getSearchedGames(String searchval, String scriteria) {
		String query = "";
		if (scriteria.matches("location")) {
			query = "SELECT * FROM Games g, Locations l WHERE l.location_name LIKE '%" + searchval
					+ "%' AND g.location_id = l.location_id";
		} else if (scriteria.matches("allgames")) {

			query = "SELECT * FROM (Games  INNER JOIN Locations ON Games.location_id = Locations.location_id) WHERE description LIKE '%"
					+ searchval
					+ "%' OR location_name LIKE '%"
					+ searchval
					+ "%' UNION SELECT * FROM (Games  INNER JOIN Locations ON Games.location_id = Locations.location_id) WHERE name LIKE '%"
					+ searchval + "%' OR location_name LIKE '%" + searchval + "%'";
		} else {
			query = "SELECT * FROM Games WHERE " + scriteria + " LIKE '%" + searchval + "%'";
		}

		System.out.println("All searched games query: " + query);

		return executeSelectQuery(query);

	}

	/**
	 * @param limit
	 * @param id
	 * @return The list of top {@code limit} games ordered by rating created by
	 *         the user with user id of {@code id}
	 */
	public ArrayList<HashMap<String, Object>> getCreatedGames(String id, int limit) {
		String query = "";
		if (limit != 0)
			query = "SELECT * FROM Games WHERE creator_id = " + id + " ORDER BY rating DESC LIMIT " + limit;
		else
			query = "SELECT * FROM Games WHERE creator_id = " + id + " ORDER BY rating DESC";

		System.out.println("Get created games query: " + query);

		return executeSelectQuery(query);

	}

	/**
	 * @param gameId
	 * @return All information of the games with game id of {@code gameId}
	 */
	public ArrayList<HashMap<String, Object>> getAllInformationOfGame(String gameId) {
		String query = "SELECT g.*, l.location_name, u.nickname FROM Games g, Locations l, Users u WHERE g.game_id = "
				+ gameId + " AND u.user_id=g.creator_id AND l.location_id=g.location_id";
		System.out.println(query);
		return executeSelectQuery(query);
	}

	/**
	 * @param gameId
	 * @return All game level information of the games with game id of
	 *         {@code gameId}
	 */
	public ArrayList<HashMap<String, Object>> getAllLevelsOfGame(String gameId) {
		String query = "SELECT gl.* FROM GameLevels gl WHERE gl.game_id = " + gameId + " ORDER BY gl.level_id ASC";
		System.out.println(query);
		return executeSelectQuery(query);
	}

	public ArrayList<HashMap<String, Object>> getUserLevelHistory(String gameId, String nickname) {
		String query = "SELECT gl.level_id, gl.hint, gl.level_code, gl.qr_address FROM PlayerHistory ph, GameLevels gl, Users u WHERE ph.game_id="
				+ gameId
				+ " AND ph.user_id=u.user_id AND u.nickname='"
				+ nickname
				+ "' AND ph.level_id = gl.level_id AND ph.game_id = gl.game_id ORDER BY gl.level_id ASC";
		System.out.println(query);
		return executeSelectQuery(query);
	}

	/**
	 * @param userId
	 * @return The user nickname whose id is {@code userId}
	 */
	public String get_nicknamefrom_id(String userId) {
		String query = "SELECT nickname FROM Users WHERE user_id = " + userId;
		try {
			return returnSingleResultRow(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	// birden fazla seyi arraylist olarak dondurmece
	public ArrayList<HashMap<String, Object>> executeSelectQuery(String query) { // EXECUTESELECTQUERY!
		try {
			ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
			statement = connection.createStatement();
			resultset = statement.executeQuery(query);
			ResultSetMetaData md = resultset.getMetaData();

			while (resultset.next()) {
				HashMap<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < md.getColumnCount(); i++) {
					map.put(md.getColumnName(i + 1), resultset.getObject(i + 1));
				}
				list.add(map);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				statement.close();
				resultset.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/*** END OF ARRAY LIST ***/
	@SuppressWarnings("finally")
	public int executeUpdateQuery(String query) {
		Statement st = null;
		int affectedRows = 0;
		try {
			st = connection.createStatement();
			affectedRows = st.executeUpdate(query);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return affectedRows;
		}
	}

	// insertion icin
	public boolean execute(String arg) throws SQLException {
		try {
			statement = connection.createStatement();
			int n = statement.executeUpdate(arg);
			if (n > 0)
				System.out.println("Record is succesfully added.");
			statement.close();
			return true;

		} catch (Exception ioe) {
			System.out.println("Record could not be added. Please try again " + arg);
			ioe.printStackTrace();
			return false;
		}
	}

	/**
	 * @param nickname
	 * @param password
	 * @return If the user can log in successfully with {@code nickname} and
	 *         {@code password} it returns true
	 */
	public boolean successfulLogin(String nickname, String password) throws Exception {
		String arg = "SELECT * FROM Users WHERE nickname='" + nickname + "' AND password='" + password + "'";
		try {
			statement = connection.createStatement();
			resultset = statement.executeQuery(arg);

			if (resultset.next()) // if there is a user
			{
				return true;
			} else
				// if there is not such a user
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			connection.close(); // connection is closed
			return false;
		} finally {
			try {
				statement.close();
				resultset.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * Checks if an email is in the database
	 * 
	 * @param email
	 *            email to search
	 * @return true, if the email is in the database. False, otherwise.
	 * @throws Exception
	 */
	public boolean isEmailInDatabase(String email) throws Exception {
		String arg = "SELECT * FROM Users WHERE email='" + email + "'";
		try {
			statement = connection.createStatement();
			resultset = statement.executeQuery(arg);

			if (resultset.next()) // email is in the database
			{
				return true;
			} else
				// if there is no registered user with that email
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			connection.close(); // connection is closed
			return false;
		} finally {
			try {
				statement.close();
				resultset.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Updates password of a user with a given email.
	 * 
	 * @param email
	 *            email of the user(to find the user in the database)
	 * @param passwd
	 *            new password
	 * @return True, if update is successful. False, otherwise.
	 * @throws Exception
	 */
	public boolean updatePasswordByEmail(String email, String passwd) throws Exception {
		String sql = "UPDATE Users SET password='" + Util.getMD5Hash(passwd) + "' WHERE email='" + email + "'";
		try {
			statement = connection.createStatement();
			int changedRows = statement.executeUpdate(sql);
			if (changedRows == 1) // updates successfully
			{
				return true;
			} else
				// cannot update
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			connection.close(); // connection is closed
			return false;
		} finally {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void close() {
		if (connection != null) {
			try {
				if (!connection.getAutoCommit())
					connection.commit();
				connection.close();
			} catch (SQLException e) {
				System.out.println("Problem in closing the database connection: " + e.getMessage());
			}
			connection = null;
		}
	}

	public String getUserCurrentLevel(String nickname, String gameId) {
		//
		// this function retrieves the current level of the user in the given
		// game. this is the level with the min level_id which the user has not
		// completed

		String query = "SELECT MIN(gl.level_id) FROM (SELECT gl.* FROM GameLevels gl, GamePlayers gp, Users u"
				+ " WHERE gl.game_id=gp.game_id AND gp.user_id=u.user_id AND u.nickname='" + nickname
				+ "') gl LEFT JOIN (SELECT ph.* FROM PlayerHistory ph, Users u WHERE ph.game_id=" + gameId
				+ " AND ph.user_id=u.user_id AND u.nickname='" + nickname
				+ "') ph ON gl.level_id = ph.level_id WHERE ph.level_id IS NULL AND gl.game_id=" + gameId;

		try {
			String result = returnSingleResultRow(query);

			if (result == null)
				result = "";
			return result;
		} catch (SQLException e) {
			return "";
		}
	}

	// returns a column of the row specified by the keys game_id and level_id in
	// "gamelevels" table
	public String getLevelInformation(LevelInformation informationType, String gameId, String levelId) {

		String query = "SELECT gl." + informationType.queryString() + " FROM GameLevels gl WHERE gl.game_id=" + gameId
				+ " AND gl.level_id=" + levelId;

		try {
			String result = returnSingleResultRow(query);

			if (result == null)
				result = "";
			return result;
		} catch (SQLException e) {
			return "";
		}
	}

	/**
	 * 
	 * @param gameId
	 *            of the game being checked
	 * @param nickname
	 *            of the player who plays the game
	 * @return true if the player completed all the levels, false otherwise
	 */
	public boolean isGameFinished(String gameId, String nickname) {

		String isFinished;

		String query = "SELECT IF(t1.count=t2.count, true, false) AS finished FROM (SELECT COUNT(*) AS count FROM PlayerHistory pl, Users u WHERE pl.game_id="
				+ gameId
				+ " AND pl.user_id=u.user_id AND u.nickname='"
				+ nickname
				+ "') t1, (SELECT g.level_count AS count FROM Games g WHERE g.game_id=" + gameId + ") t2";

		try {
			isFinished = returnSingleResultRow(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		if (Util.hasTextContent(isFinished)) {
			return isFinished.equalsIgnoreCase("1") ? true : false;
		}
		return false;
	}

	public boolean userExists(String nickname, String email) {

		String query = "SELECT COUNT(*) FROM Users u WHERE u.nickname='" + nickname + "' OR u.email='" + email + "'";
		String count = null;
		try {
			count = returnSingleResultRow(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		if (Util.hasTextContent(count)) {
			return count.equalsIgnoreCase("0") ? false : true;
		}
		return false;
	}

	// checks if the user is registered to the given game
	public boolean isUserRegisteredToGame(String nickname, String gameId) {

		String query = "SELECT gp.game_id FROM GamePlayers gp, Users u WHERE gp.game_id=" + gameId
				+ " AND gp.user_id=u.user_id AND u.nickname='" + nickname + "'";
		try {
			String result = returnSingleResultRow(query);

			if (Util.hasTextContent(result)) {
				if (result.equalsIgnoreCase(gameId)) {
					return true;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		return false;
	}

	// checks if the user is the owner of the game
	public boolean userOwnsTheGame(String nickname, String gameId) {

		String query = "SELECT g.game_id FROM Games g, Users u WHERE g.game_id=" + gameId
				+ " AND g.creator_id=u.user_id AND u.nickname='" + nickname + "'";
		try {
			String result = returnSingleResultRow(query);

			if (Util.hasTextContent(result)) {
				if (result.equalsIgnoreCase(gameId)) {
					return true;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		return false;
	}

	/**
	 * This function registers the user specified by {@code nickname} to the
	 * game specified by {@code gameId}.
	 * 
	 * @param nickname
	 *            Nickname of the user
	 * @param gameId
	 *            GameID of the game to be registered to.
	 * @return true if the user is successfuly registered to the game, false
	 *         otherwise.
	 */
	public boolean registerToGame(String nickname, String gameId, String userbegins) {

		String query = "INSERT INTO GamePlayers (game_id, user_id) VALUES(" + gameId
				+ ", (SELECT MIN(u.user_id) FROM Users u WHERE u.nickname='" + nickname + "'))";
		try {
			execute(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	/**
	 * 
	 * @param gameId
	 *            GameID of the game to be checked.
	 * @param keyword
	 *            Keyword to be searched among the game levels.
	 * @return The level ID if {@code keyword} matches any of the keywords of
	 *         the levels, empty string otherwise.
	 */
	public String checkAllLevelKeywords(String gameId, String keyword) {

		String query = "SELECT gl.level_id FROM GameLevels gl WHERE gl.game_id=" + gameId + " AND level_code='"
				+ keyword + "'";

		String levelId = "";
		try {
			levelId = returnSingleResultRow(query);
		} catch (SQLException e) {
			e.printStackTrace();
			levelId = "";
		}

		return levelId;
	}

	/**
	 * This function records the rating of the user specified by
	 * {@code nickname} for the game specified by {@code gameId}. <br/>
	 * Initially every [game, registered user] record will have a NULL rating
	 * value.
	 * 
	 * @param nickname
	 *            Nickname of the user
	 * @param gameId
	 *            GameID of the game to be rated.
	 * @param rating
	 *            An integer between 1 and 5 that indicates the user's rating of
	 *            the game
	 * @return true if the rating is successfuly recorded to the database, false
	 *         otherwise.
	 */
	public boolean rateGame(String nickname, String gameId, String rating) {

		String userId = this.getUserInformation(DatabaseManager.UserInformation.ID, nickname);
		String query = "UPDATE GamePlayers SET rating=" + rating + " WHERE game_id=" + gameId + " AND user_id="
				+ userId;

		boolean success = true;
		try {
			if (execute(query)) {
				success = updateGameRating(gameId);
			} else {
				success = false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		return success;
	}

	/**
	 * This function calculates the average of all the ratings by game players
	 * for the game specified by {@code gameId}.
	 * 
	 * @param gameId
	 *            GameID of the game which its rating will be calculated and
	 *            updated.
	 * @return true if the rating is successfuly calculated and updated, false
	 *         otherwise.
	 */
	private boolean updateGameRating(String gameId) {

		String query = "UPDATE Games SET rating=(SELECT AVG(rating) FROM GamePlayers WHERE game_id=" + gameId
				+ ") WHERE game_id=" + gameId;

		try {
			return execute(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

	}

	/**
	 * This function gets the user's rating for the game specified by
	 * {@code gameId}.
	 * 
	 * @param gameId
	 *            GameID of the game which the user rated.
	 * @param nickname
	 *            Nickname of the user.
	 * @return The rating value that the user voted for the game.
	 */
	public float getRatingByUser(String gameId, String nickname) {

		String query = "SELECT gp.rating FROM GamePlayers gp, Users u WHERE gp.game_id=" + gameId
				+ " AND gp.user_id=u.user_id AND u.nickname='" + nickname + "'";

		float rating = 0;
		try {
			String ratingStr = returnSingleResultRow(query);
			if (!Util.hasTextContent(ratingStr)) {
				return 0;
			}
			rating = Float.parseFloat(ratingStr);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} catch (NumberFormatException e) {
			e.printStackTrace();
			return 0;
		}

		return rating;
	}

	/**
	 * 
	 * @return The users who has won the games most.
	 */
	public ArrayList<HashMap<String, Object>> getMostWinnerUsers(int limit) {

		String query = "SELECT nickname,COUNT(*) as games_finished"
				+ " FROM (SELECT t1.*, IF(t1.level_count=t2.level_count, true,false) AS finished"
				+ " FROM (SELECT pl.game_id, u.user_id, u.nickname,COUNT(*) AS level_count FROM PlayerHistory pl, Users u WHERE pl.user_id=u.user_id GROUP BY pl.game_id,pl.user_id) t1"
				+ " ,(SELECT g.level_count, g.game_id FROM Games g) t2"
				+ " WHERE t1.game_id=t2.game_id) gw WHERE gw.finished=true"
				+ " GROUP BY nickname ORDER BY games_finished DESC, user_id ASC LIMIT " + limit;

		ArrayList<HashMap<String, Object>> mostWinnerUsers = this.executeSelectQuery(query);

		if (mostWinnerUsers == null) {
			return new ArrayList<HashMap<String, Object>>();
		}

		return mostWinnerUsers;
	}

	/**
	 * 
	 * @return The users who has registered to games most.
	 */
	public ArrayList<HashMap<String, Object>> getUsersRegisteredToGamesMost(int limit) {

		String query = "SELECT gr.nickname, gr.game_count" + " FROM (SELECT u.nickname,COUNT(*) AS game_count"
				+ " FROM GamePlayers gp, Users u WHERE gp.user_id=u.user_id"
				+ " GROUP BY gp.user_id ORDER BY game_count DESC, u.user_id ASC LIMIT " + limit + ") gr";

		ArrayList<HashMap<String, Object>> mostRegisteredUsers = this.executeSelectQuery(query);

		if (mostRegisteredUsers == null) {
			return new ArrayList<HashMap<String, Object>>();
		}

		return mostRegisteredUsers;
	}

	/**
	 * 
	 * @return The games with the highest ratings of all times.
	 */
	public ArrayList<HashMap<String, Object>> getTopRatedGames(int limit) {

		String query = "SELECT g.name, g.rating FROM Games g ORDER BY g.rating DESC, g.game_id ASC LIMIT " + limit;

		ArrayList<HashMap<String, Object>> topRatedGames = this.executeSelectQuery(query);

		if (topRatedGames == null) {
			return new ArrayList<HashMap<String, Object>>();
		}

		return topRatedGames;
	}

	/**
	 * 
	 * @return The users who has created games most.
	 */
	public ArrayList<HashMap<String, Object>> getUsersCreatedGamesMost(int limit) {

		String query = "SELECT gc.nickname, gc.game_count FROM (SELECT u.nickname, COUNT(*) AS game_count"
				+ " FROM Games g, Users u WHERE g.creator_id=u.user_id"
				+ " GROUP BY u.user_id ORDER BY game_count DESC, u.user_id ASC LIMIT " + limit + ") gc";

		ArrayList<HashMap<String, Object>> usersCreatedGamesMost = this.executeSelectQuery(query);

		if (usersCreatedGamesMost == null) {
			return new ArrayList<HashMap<String, Object>>();
		}

		return usersCreatedGamesMost;
	}

	/**
	 * 
	 * @return The games with most registered users.
	 */
	public ArrayList<HashMap<String, Object>> getGamesWithMostPlayers(int limit) {

		String query = "SELECT gr.name, gr.player_count" + " FROM (SELECT g.name,COUNT(*) AS player_count"
				+ " FROM GamePlayers gp, Games g WHERE gp.game_id=g.game_id"
				+ " GROUP BY gp.game_id ORDER BY player_count DESC, g.game_id ASC LIMIT " + limit + ") gr";

		ArrayList<HashMap<String, Object>> gamesWithMostPlayers = this.executeSelectQuery(query);

		if (gamesWithMostPlayers == null) {
			return new ArrayList<HashMap<String, Object>>();
		}

		return gamesWithMostPlayers;
	}

	/**
	 * This functions counts the users who has completed a certain game.
	 * 
	 * @param gameId
	 *            {@code game_id} of the game
	 * @return Number of users who has completed the game specified by
	 *         {@code gameId}, or 0 in case of any problems.
	 */
	public int getNumPLayersFinishedGame(String gameId) {

		String query = "SELECT COUNT(*) as players_finished"
				+ " FROM (SELECT t1.*, IF(t1.level_count=t2.level_count, true,false) AS finished"
				+ " FROM (SELECT pl.game_id, COUNT(*) AS level_count"
				+ " FROM PlayerHistory pl GROUP BY pl.game_id,pl.user_id) t1"
				+ " ,(SELECT g.level_count, g.game_id FROM Games g) t2"
				+ " WHERE t1.game_id=t2.game_id) gw WHERE gw.finished=true and gw.game_id=" + gameId;

		int playerCount = 0;
		try {
			String playerCountStr = returnSingleResultRow(query);
			if (!Util.hasTextContent(playerCountStr)) {
				return 0;
			}
			playerCount = Integer.parseInt(playerCountStr);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} catch (NumberFormatException e) {
			e.printStackTrace();
			return 0;
		}

		return playerCount;
	}

	/**
	 * This function counts the number of players who has completed each level
	 * of a certain game.
	 * 
	 * @param gameId
	 *            {@code game_id} of the game
	 * @return an {@code ArrayList<HashMap<String, Object>>} which holds all the
	 *         rows that contains the {@code level_id} and the number of players
	 *         who has completed the corresponding level, <br/>
	 *         or an empty {@code ArrayList<HashMap<String, Object>>} in case of
	 *         any problems.
	 */
	public ArrayList<HashMap<String, Object>> getNumPlayersFinishedLevels(String gameId) {

		String query = "SELECT t1.level_id, IF(t2.player_count IS NULL, 0,t2.player_count) AS player_count"
				+ " FROM (SELECT gl.level_id FROM GameLevels gl WHERE gl.game_id=" + gameId + ") t1"
				+ " LEFT JOIN (SELECT ph.level_id, COUNT(*) AS player_count FROM PlayerHistory ph WHERE ph.game_id="
				+ gameId + " GROUP BY ph.level_id ORDER BY ph.level_id ASC) t2" + " ON t1.level_id=t2.level_id";

		ArrayList<HashMap<String, Object>> numPlayersFinishedLevels = this.executeSelectQuery(query);

		if (numPlayersFinishedLevels == null) {
			return new ArrayList<HashMap<String, Object>>();
		}

		return numPlayersFinishedLevels;

	}

	public boolean gameDateExpired(String gameId) {

		String endDateStr = getGameInformation(DatabaseManager.GameInformation.END_DATE, gameId);
		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		Date endDate;

		try {
			endDate = (Date) formatter.parse(endDateStr);
		} catch (ParseException e) {
			e.printStackTrace();
			return true;
		}

		Date currentDate = new Date();
		if (currentDate.compareTo(endDate) > 0) {
			return true;
		}

		return false;
	}

	public void startUserGameTimer(String nickname, String gameId) {

		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String currentDate = df.format(new Date());

		String query = "UPDATE GamePlayers SET user_begins='" + currentDate + "' WHERE game_id=" + gameId
				+ " AND user_id=(SELECT MAX(u.user_id) FROM Users u WHERE u.nickname='" + nickname
				+ "') AND user_begins is NULL";

		try {
			execute(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public boolean setGameActive(String active, String nickname, String gameId) {

		String query = "UPDATE Games SET is_active=" + active + " WHERE game_id=" + gameId
				+ " AND creator_id=(SELECT u.user_id FROM Users u WHERE u.nickname='" + nickname + "')";

		boolean success = true;
		try {
			success = execute(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		
		return success;
	}
}
